#!/bin/bash

echo "Populating database..."
max=10000

n_companies=$(cat companies.txt | head -n $max | wc -l)
n_cities=$(cat cities.txt | head -n $max | wc -l)
i=1
cat companies.txt | head -n $max | while read name
do
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO companies (name) VALUES ('$name');"
  list=$(mktemp)
	for k in $(seq 1 3)
	do
	  n=$((RANDOM % $n_cities + 1))
		city=$(head -$n cities.txt | tail -n 1)
		echo "$city" >> $list
	done
	cat $list | uniq | while read city
	do
	  mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO branch_offices (city_name, company_id) VALUES ('$city', $i);"
	done
	i=$((i+1))
done

n_branch_offices=$(mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "select count(*) as n from branch_offices" -E | tail -n 1 | sed 's/n: //g')

n_jobs=$(cat jobs.txt | head -n $max | wc -l)
cat jobs.txt | head -n $max | while read name
do
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO jobs (name) VALUES ('$name');"
done

n_roles=$(cat roles.txt | head -n $max | wc -l)
cat roles.txt | head -n $max | while read name
do
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO roles (name) VALUES ('$name');"
done

n_departments=$(cat departments.txt | head -n $max | wc -l)
cat departments.txt | head -n $max | while read name
do
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO departments (name) VALUES ('$name');"
done

n_genres=$(cat genres.txt | wc -l)
n_employees=$(cat first-names.txt | head -n $max | wc -l)
cat first-names.txt | head -n $max | while read name
do
	login=$(echo "$name" | awk '{print tolower($0)}')
	password=$RANDOM
	hours=$(($(($RANDOM % 3 + 2)) * 10)).0 # 20, 30 or 40
	salary=$RANDOM.00
	branch_id=$(($RANDOM % $n_branch_offices + 1))
	job_id=$(($RANDOM % $n_jobs + 1))
	department_id=$(($RANDOM % $n_departments + 1))
	role_id=$(($RANDOM % $n_roles + 1))
	genre_id=$(($RANDOM % $n_genres + 1))
	genre=$(head -$genre_id genres.txt | tail -n 1)
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO employees (name, login, password, hours, salary, branch_office_id, job_id, department_id, role_id, genre) VALUES ('$name', '$login', '$password', $hours, $salary, $branch_id, $job_id, $department_id, $role_id, '$genre');"
done

for i in $(seq 1 $max)
do
	employee_id=$(($RANDOM % $n_employees + 1))
	hours=$(($RANDOM % 20 + 1)).0
	date=$(date '+%Y-%m-%d')
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO hour_bank (employee_id, hours, date) VALUES ($employee_id, $hours, '$date');"
done

n_in_or_out=$(cat in_or_out.txt | wc -l)
for i in $(seq 1 $max)
do
	in_or_out=$(($RANDOM % $n_in_or_out + 1))
	io=$(head -$in_or_out in_or_out.txt | tail -n 1)
	employee_id=$(($RANDOM % $n_employees + 1))
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO hours (employee_id, ip, in_or_out) VALUES ($employee_id, '127.0.0.1', '$io');"
done

for i in $(seq 1 $max)
do
	branch_id=$(($RANDOM % $n_branch_offices + 1))
	department_id=$(($RANDOM % $n_departments + 1))
	mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' -e "INSERT INTO branch_office_has_department (department_id, branch_office_id) VALUES ($department_id, $branch_id);"
done

mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' < offs.sql

mysql -u caiosba -h disciplinas.dcc.ufba.br -D MATB09_caiosba -p'suasenha' < holidays.sql

echo "Populated!"
